'''
Created on 2011-5-19

@author: elm
'''
import unittest,xmlrpclib
from ddbms.SqlFormatter import SqlFormatter
from ddbms.SqlStatement import SqlStatement
from ddbms.StaticSchema import StaticSchema
from ddbms.ExecuterSite import ExecuterSite
from ddbms.URIUtil import parseConn
class DdbmsTest(unittest.TestCase):

    test_cases = [
        {'sql':'select * from Customer',
         'result':'SELECT  *\nFROM    [customer]',
         'resultRA':'[customer]',
         'count':15000
        },
        
        {'sql':'select publisher.name  from Publisher',
         'result':'SELECT  [publisher].[name]\nFROM    [publisher]',
         'resultRA':'(PROJECTION:[[publisher].[name]] [publisher])',
         'count':5000},
        
        {'sql':'select Book.title  from Book  where copies > 5000',
         'result':'SELECT  [book].[title]\nFROM    [book]\nWHERE   _default.[copies] > 5000',
         'resultRA':'(PROJECTION:[[book].[title]] (SELECT:_default.[copies] > 5000 [book]))',
         'count':25044},
        
        {'sql':'select  customer_id, quantity  from Orders  where quantity < 8',
         'result':'SELECT  _default.[customer_id], _default.[quantity]\nFROM    [orders]\nWHERE   _default.[quantity] < 8',
         'resultRA':'(PROJECTION:[_default.[customer_id],_default.[quantity]] (SELECT:_default.[quantity] < 8 [orders]))',
         'count':72620},
        
        {'sql':'''select Book.title,Book.copies, Publisher.name, Publisher.nation  from Book,Publisher  where Book.publisher_id=Publisher.id and Publisher.nation='USA' and  Book.copies > 1000
               ''',
         'result':'SELECT  [book].[title], [book].[copies], [publisher].[name], [publisher].[nation]\nFROM    [book], [publisher]\nWHERE   (([book].[publisher_id] = [publisher].[id] AND [publisher].[nation] = \'usa\') AND [book].[copies] > 1000)',
         'resultRA':'(PROJECTION:[[book].[title],[book].[copies],[publisher].[name],[publisher].[nation]] (SELECT:[book].[copies] > 1000 (SELECT:[publisher].[nation] = \'usa\' ([book] JOIN [publisher] ON [book].[publisher_id]=[publisher].[id]))))',
         'count':22085},
        
        {'sql':'''    select         Customer.name,Orders.quantity
            from         Customer,Orders
            where         Customer.id=Orders.customer_id''',
         'result':'SELECT  [customer].[name], [orders].[quantity]\nFROM    [customer], [orders]\nWHERE   [customer].[id] = [orders].[customer_id]',
         'resultRA':'(PROJECTION:[[customer].[name],[orders].[quantity]] ([customer] JOIN [orders] ON [customer].[id]=[orders].[customer_id]))',
         'count':100000},
        
        {'sql':'''
                    select         Customer.name ,Orders.quantity,Book.title
    from         Customer,Orders,Book
    where         Customer.id=Orders.customer_id and 
    Book.id=Orders.book_id and 
    Customer.rank=1 and
Book.copies>5000''', 
         'result':'''SELECT  [customer].[name], [orders].[quantity], [book].[title]\nFROM    [customer], [orders], [book]\nWHERE   ((([customer].[id] = [orders].[customer_id] AND [book].[id] = [orders].[book_id]) AND [customer].[rank] = 1) AND [book].[copies] > 5000)''',
         'resultRA':'(PROJECTION:[[customer].[name],[orders].[quantity],[book].[title]] (SELECT:[book].[copies] > 5000 (SELECT:[customer].[rank] = 1 ([book] JOIN ([customer] JOIN [orders] ON [customer].[id]=[orders].[customer_id]) ON [book].[id]=([customer] JOIN [orders] ON [customer].[id]=[orders].[customer_id]).[book_id]))))',
         'count':19966}
    ]
    
    
#    def testFormatter(self):
#        id = 1
#        formatter = SqlFormatter()
#        for case in self.test_cases:
#            ret = formatter.parse(case['sql'])
#            self.assertEqual(ret, case['result'], "TEST FAILED: #" + str(id))
#            id = id + 1       
    
    '''def testRA(self):
        id = 1
        ss=SqlStatement()
        for case in self.test_cases:
            ret = fromSqlStatement(ss.parse(case['sql']))
            self.assertEqual(str(ret), case['resultRA'], "TEST FAILED: #" + str(id))
            id = id + 1 ''' 
#            
#    def testStaticSchema(self):
#        ss = StaticSchema()        
    
#    def testExpand(self):
#        schema = StaticSchema()
#        executer = ExecuterSite(schema)
#        for case in self.test_cases:
#            print(
#                  executer.execute(
#                    executer.optimize(    
#                        executer.expand(
#                            executer.parse(case['sql'])
#                )
#              )
#            )
#                  )
            
#    def testExecuteLocal(self):
#        schema = StaticSchema()
#        mysql = ("localhost","root","","ddb")
#        es = ExecuterSite(schema,mysql)
#        for case in self.test_cases:
#            ex = es.allocate(es.optimize(es.expand(es.parse(case['sql']))))
#            result = es.execute(ex)
#            print(case['sql'])            
#            print(result.eval())
    
    def doTestSite(self,i):
        print "Starting test Site%d"%i
        site = xmlrpclib.ServerProxy(parseConn("localhost:1010%d"%i),allow_none = 1)
        for case in self.test_cases:
            result = site.executeSql(case['sql'],True)
            print ("sql: %s"%case['sql'])
            self.assertEqual(case['count'],result["resultLength"])
#            print ('''
#Schema: %s
#
#Sql: 
#%s
#
#Parsed Relational Algebra Expression:
#%s
#
#Unoptimized:
#%s
#
#Optimized:
#%s
#
#Execution time:
#%s seconds
#
#Result:
#%s
#
#                    '''%(result["schema"],result["sql"],result["beforeExpand"],result["beforeOptimize"],result["afterOptimize"],result["time"],result["result"]))
    
    def testSite1(self):
        self.doTestSite(1)
        
    def testSite2(self):
        self.doTestSite(2)
        
    def testSite3(self):
        self.doTestSite(3)
        
    def testSite4(self):
        self.doTestSite(4)
        

if __name__ == "__main__":
    #import sys;sys.argv = ['', 'Test.testName']
    unittest.main()
